package com.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

public class SqlUtils {
    /**
     * 多表分页模糊查询
     * @param c1      主表
     * @param c2      副表
     * @param strings 从副表中查询的数据
     * @return 返回sql语句
     */
    public static String pageSql(Class c1, Class c2, String[] strings) {
        List<String> arrayList = new ArrayList<>();
        for (String s : strings) {
            arrayList.add(s);
        }
        arrayList.add("Class");
        String order = null;
        List<String> c1Fields = new ArrayList<>();
        List<String> c2Fields = new ArrayList<>();
        Method[] c1Methods = c1.getMethods();
        Method[] c2Methods = c2.getMethods();
        StringBuilder linke = new StringBuilder();
        StringBuilder sql = new StringBuilder("select ");
        for (Method method : c1Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                c1Fields.add(fieldName);
            }
        }
        for (Method method : c2Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(c2.getSimpleName() + fieldName)) {
                    c2Fields.add(fieldName);
                }
            }
        }
        try {
            c1.getDeclaredField(c1.getSimpleName() + "Id");
            linke.append(c1.getSimpleName()).append(linke).append(".id").append("=").append(c2.getSimpleName()).append(".").append(c1.getSimpleName()).append("Id");
        } catch (NoSuchFieldException e) {
            linke.append(c1.getSimpleName()).append(".").append(c2.getSimpleName()).append("id").append("=").append(c2.getSimpleName()).append(".id");
        }
        for (String field : c1Fields) {
            if (arrayList.contains(field)) {
                continue;
            } else if (order == null && field.contains("Time")) {
                order = field;
            }
            sql.append(c1.getSimpleName()).append(".").append(field).append(",");
        }
        for (String field : c2Fields) {
            sql.append(c2.getSimpleName()).append(".").append(field).append(" ").append(c2.getSimpleName()).append(field).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ").append(c1.getSimpleName()).append(" left join ").append(c2.getSimpleName()).append(" on ").append(linke).append(" where concat(").toString();
        for (String field : c1Fields) {
            sql.append("ifnull(").append(c1.getSimpleName()).append(".").append(field).append(",' '),' ',");
        }
        for (String field : c2Fields) {
            sql.append("ifnull(").append(c2.getSimpleName()).append(".").append(field).append(",' '),' ',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")like ? order by ").append(c1.getSimpleName()).append(".").append(order == null ? "id" : order + " desc").append(" LIMIT ?,?");
        return String.valueOf(sql);
    }

    /**
     * 单表分页模糊查询
     * @param c 要查询的表
     * @return 返回sql语句
     */

    public static String pageSql(Class c, String[] strings) {
        List<String> arrayList = new ArrayList<>();
        if (strings != null) {
            for (String s : strings) {
                arrayList.add(s);
            }
        }
        arrayList.add("Class");
        String order = null;
        List<String> fields = new ArrayList<>();
        Method[] methods = c.getMethods();
        StringBuilder sql = new StringBuilder("select ");
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                fields.add(fieldName);
            }
        }
        for (String field : fields) {
            if (order == null && field.contains("Time")) {
                order = field;
            }
            sql.append(field).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ").append(c.getSimpleName()).append(" where concat(").toString();
        for (String field : fields) {
            sql.append("ifnull(").append(c.getSimpleName()).append(".").append(field).append(",' '),' ',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")like ? order by ").append(c.getSimpleName()).append(".").append(order == null ? "id" : order + " desc").append(" LIMIT ?,?");
        System.out.println(sql);
        return String.valueOf(sql);
    }

    /**
     * 多表模糊查询到的条数
     * @param c1      主表
     * @param c2      副表
     * @param strings 从副表中查询的数据
     * @return 返回sql语句
     */
    public static String countSql(Class c1, Class c2, String[] strings) {
        List<String> arrayList = new ArrayList<>();
        for (String s : strings) {
            arrayList.add(s);
        }
        arrayList.add("Class");
        List<String> c1Fields = new ArrayList<>();
        List<String> c2Fields = new ArrayList<>();
        Method[] c1Methods = c1.getMethods();
        Method[] c2Methods = c2.getMethods();
        StringBuilder linke = new StringBuilder();
        StringBuilder sql = new StringBuilder();
        for (Method method : c1Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                c1Fields.add(fieldName);
            }
        }
        for (Method method : c2Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(c2.getSimpleName() + fieldName)) {
                    c2Fields.add(fieldName);
                }
            }
        }
        try {
            c1.getDeclaredField(c1.getSimpleName() + "Id");
            linke.append(c1.getSimpleName()).append(linke).append(".id").append("=").append(c2.getSimpleName()).append(".").append(c1.getSimpleName()).append("Id");
        } catch (NoSuchFieldException e) {
            linke.append(c1.getSimpleName()).append(".").append(c2.getSimpleName()).append("id").append("=").append(c2.getSimpleName()).append(".id");
        }
        sql.append("select count(1) from ").append(c1.getSimpleName()).append(" left join ").append(c2.getSimpleName()).append(" on ").append(linke).append(" where concat(").toString();

        for (String field : c1Fields) {
            sql.append("ifnull(").append(c1.getSimpleName()).append(".").append(field).append(",' '),' ',");
        }
        for (String field : c2Fields) {
            sql.append("ifnull(").append(c2.getSimpleName()).append(".").append(field).append(",' '),' ',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")like ?");
        return String.valueOf(sql);
    }

    /**
     * 单表模糊查询到的条数
     * @param c 要查询的表
     * @return 返回sql语句
     */
    public static String countSql(Class c) {
        List<String> arrayList = new ArrayList<>();
        arrayList.add("Class");
        Method[] methods = c.getMethods();
        List<String> fields = new ArrayList();
        StringBuilder sql = new StringBuilder();
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                fields.add(fieldName);
            }
        }
        sql.append("select count(1) from ").append(c.getSimpleName()).append(" where concat(");
        for (String field : fields) {
            sql.append("ifNull(").append(field).append(",' '),' ',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")like ?");
        return String.valueOf(sql);
    }


    /**
     * 多表按需求查询
     * @param obj     主表对象
     * @param c2      副表类
     * @param strings 从副表中查询的数据
     * @return 返回sql语句
     */
    public static Map.Entry<String, Object[]> selectBy(Object obj, Class c2, String[] strings) {
        List list = new ArrayList();
        List<String> arrayList = new ArrayList<>();
        for (String s : strings) {
            arrayList.add(s);
        }
        arrayList.add("Class");
        Class c1 = obj.getClass();
        List<String> c1Fields = new ArrayList<>();
        List<String> c2Fields = new ArrayList<>();
        Method[] c1Methods = c1.getMethods();
        Method[] c2Methods = c2.getMethods();
        StringBuilder sql = new StringBuilder("select ");
        for (Method method : c1Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                c1Fields.add(fieldName);
            }
        }
        for (Method method : c2Methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(c2.getSimpleName() + fieldName)) {
                    c2Fields.add(fieldName);
                }
            }
        }
        for (String field : c1Fields) {
            if (!arrayList.contains(field)) {
                sql.append(c1.getSimpleName()).append(".").append(field).append(",");
            }
        }
        for (String s : arrayList) {
            if (c2.getSimpleName().length()>s.length()) {
                continue;
            }
            String fieldName = s.substring(c2.getSimpleName().length());
            if (c2Fields.contains(fieldName)) {
                sql.append(c2.getSimpleName()).append(".").append(fieldName).append(" ").append(s).append(",");
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ").append(c1.getSimpleName()).append(" left join ").append(c2.getSimpleName()).
                append(" on ").append(c1.getSimpleName()).append(".").append(c2.getSimpleName()).
                append("id=").append(c2.getSimpleName()).append(".id where 1=1");
        arrayList.remove("Class");
        c1Fields.addAll(arrayList);
        for (String field : c1Fields) {
            Object object = null;
            try {
                Method method = c1.getMethod("get" + field);
                object = method.invoke(obj);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (object != null) {
                sql.append(" and ").append(c1.getSimpleName()).append(".").append(field).append(" = ?");
                list.add(object);
            }
        }
        for (String field : c2Fields) {
            Object object = null;
            try {
                Method method = c1.getMethod("get" + c2.getSimpleName() + field);
                object = method.invoke(obj);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (object != null) {
                sql.append(" and ").append(c2.getSimpleName()).append(".").append(field).append(" = ?");
                list.add(object);
            }
        }
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }

    /**
     * 单表按需求查询
     * @param obj 对象
     * @return 返回sql语句
     */
    public static Map.Entry<String, Object[]> selectBy(Object obj, String[] strings) {
        List list = new ArrayList();
        List<String> arrayList = new ArrayList<>();
        if (strings != null) {
            for (String s : strings) {
                arrayList.add(s);
            }
        }
        Class c = obj.getClass();
        List<String> fields = new ArrayList<>();
        Method[] methods = c.getMethods();
        StringBuilder sql = new StringBuilder("select ");
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                fields.add(fieldName);
                fields.remove("Class");
            }
        }
        for (String field : fields) {
            if (!arrayList.contains(field)) {
                sql.append(field).append(",");
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ").append(c.getSimpleName()).append(" where 1=1");
        for (String field : fields) {
            Object object = null;
            try {
                Method method = c.getMethod("get" + field);
                object = method.invoke(obj);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (object != null) {
                sql.append(" and ").append(field).append(" = ?");
                list.add(object);
            }
        }
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }

    public static Map.Entry<String, Object[]> insertSql(Object obj, String[] strings) {
        List list = new ArrayList();
        List<String> arrayList = new ArrayList<>();
        for (String s : strings) {
            arrayList.add(s);
        }
        arrayList.add("Class");
        Class c = obj.getClass();
        StringBuilder sql = new StringBuilder("insert into ");
        Method[] methods = c.getMethods();
        sql.append(c.getSimpleName()).append("(");
        int j = 0;
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                Object object = null;
                try {
                    object = method.invoke(obj);
                } catch (Exception e) {

                }
                if (object != null) {
                    sql.append("`").append(fieldName).append("`,");
                    list.add(object);
                    j++;
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") values(");
        if (j > 0) {
            sql.append("?");
        }
        for (
                int i = 0;
                i < j - 1; i++) {
            sql.append(",?");
        }
        sql.append(")");
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }

    public static Map.Entry<String, Object[]> insertSql(Object obj) {
        Class c = obj.getClass();
        List list = new ArrayList();
        StringBuilder sql = new StringBuilder("insert into ");
        Method[] methods = c.getMethods();
        sql.append(c.getSimpleName()).append("(");
        int j = 0;
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if ("Class".equals(fieldName)) {
                    continue;
                }
                Object object = null;
                try {
                    object = method.invoke(obj);
                } catch (Exception e) {

                }
                if (object != null) {
                    sql.append("`").append(fieldName).append("`,");
                    list.add(object);
                    j++;
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") values(");
        if (j > 0) {
            sql.append("?");
        }
        for (
                int i = 0;
                i < j - 1; i++) {
            sql.append(",?");
        }
        sql.append(")");
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }

    public static Map.Entry<String, Object[]> updateSql(Object obj, String[] strings) {
        List list = new ArrayList();
        Class c = obj.getClass();
        List<String> arrayList = new ArrayList<>();
        for (String s : strings) {
            arrayList.add(s);
        }
        arrayList.add("Class");
        arrayList.add("Id");
        Method[] methods = c.getMethods();
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(c.getSimpleName()).append(" set ");
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                Object object = null;
                try {
                    object = method.invoke(obj);
                } catch (Exception e) {

                }
                if (object != null) {
                    sql.append("`").append(fieldName).append("` = ?,");
                    list.add(object);
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where `id` = ?");
        try {
            list.add(c.getMethod("getId").invoke(obj));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }

    public static Map.Entry<String, Object[]> updateSql(Object obj) {
        List list = new ArrayList();
        Class c = obj.getClass();
        Method[] methods = c.getMethods();
        List<String> arrayList = new ArrayList<>();
        arrayList.add("Class");
        arrayList.add("Id");
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(c.getSimpleName()).append(" set ");
        for (Method method : methods) {
            if (method.getName().contains("get")) {
                String fieldName = method.getName().substring(3);
                if (arrayList.contains(fieldName)) {
                    continue;
                }
                Object object = null;
                try {
                    object = method.invoke(obj);
                } catch (Exception e) {

                }
                if (object != null) {
                    sql.append("`").append(fieldName).append("` = ?,");
                    list.add(object);
                }
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where `id` = ?");
        try {
            list.add(c.getMethod("getId").invoke(obj));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new AbstractMap.SimpleImmutableEntry<>(String.valueOf(sql), list.toArray());
    }
}
